﻿using Dapper;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace DapperDemo
{
    /// <summary>
    /// http://www.cnblogs.com/huangxincheng/p/5828470.html
    /// </summary>
    public class TestDemo2
    {
        private static readonly IDbConnection Conn = 
            new SqlConnection(@"Data Source = (localdb)\MSSQLLocalDB; Initial Catalog = Test; Integrated Security = True; Pooling=False");

        /// <summary>
        /// 单条insert操作
        /// </summary>
        /// <returns></returns>
        public static int InsertTest()
        {
            var result = Conn.Execute("Insert into Users values (@UserName, @Email, @Address)",
                                   new { UserName = "jack", Email = "380234234@qq.com", Address = "上海" });

            return result;
        }

        /// <summary>
        /// InsertBulk操作
        /// </summary>
        /// <returns></returns>
        public static int InsertBulkTest()
        {
            var usersList = Enumerable.Range(0, 10).Select(i => new User
            {
                Email = i + "qq.com",
                Address = "安徽",
                UserName = i + "jack"
            });

            var result = Conn.Execute("Insert into Users values (@UserName, @Email, @Address)", usersList);
            return result;   // result: 10
        }

        /// <summary>
        /// 查询操作
        /// </summary>
        /// <returns></returns>
        public static List<User> QueryTest()
        {
            var query = Conn.Query<User>("select * from users where username=@UserName",
                new { UserName = "jack" });

            return query.ToList();
        }

        /// <summary>
        /// 更新操作
        /// </summary>
        /// <returns></returns>
        public static int UpdateTest()
        {
            var result = Conn.Execute("update users set username='mary123' where userid =@userid",
                new { userId = 2 });

            return result;
        }

        /// <summary>
        /// 更新操作
        /// </summary>
        /// <returns></returns>
        public static int DeleteTest()
        {
            var result = Conn.Execute("delete from users where userid =@userId",
                new { userId = 2 });

            return result;
        }

        public class User
        {
            public int UserID { get; set; }
            public string UserName { get; set; }
            public string Email { get; set; }
            public string Address { get; set; }
        }
    }
}